package DB
{
	public class NotesDB
	{
		/// DATABASE PART.......................................
		import flash.data.SQLConnection;
		import flash.data.SQLResult;
		import flash.data.SQLStatement;
		import flash.errors.SQLError;
		import flash.events.Event;
		import flash.events.SQLErrorEvent;
		import flash.events.SQLEvent;
		import flash.filesystem.File;
		import flash.events.KeyboardEvent;
		
		import forms.Utils;
		
		import mx.collections.ArrayList;
		import mx.events.CollectionEvent;
		
		import spark.components.View;
		//SOS! Uncomment this before release!
		//var dbFile:File = File.desktopDirectory.resolvePath("GibDB.db"); //ALWAYS COMMENT  BEFORE RELEASE!
		var dbFile:File = File.applicationStorageDirectory.resolvePath("GibDB.db");
		
		public function NotesDB()
		{
			
		}
		
		var dbTemplate:File = new File();
		private  var initConn = false;
		private  var conn:SQLConnection = new SQLConnection();
		private  var createStmt:SQLStatement = new SQLStatement();
		private  var selectStmt:SQLStatement = new SQLStatement();
		private  var insertStmt:SQLStatement = new SQLStatement();
		private  var deleteStmt:SQLStatement = new SQLStatement();
		public var result:SQLResult = new SQLResult();
		public function initDB()
		{
			if (!dbFile.exists)
			{
				//dbTemplate = File.applicationStorageDirectory.resolvePath("DBTemplate.db");
				dbTemplate = File.desktopDirectory.resolvePath("notesdb.db");
				dbTemplate.copyTo(dbFile);
			}
		}
		
		public function viewActivated_handler(evt:Event)
		{ 
			if(initConn)
			{
				
			}
		}
		
		public  function realInit()
		{
			if(conn.connected==true)
			{}
			else
			{
				try
				{
					conn.open(dbFile);
					tables();
				}
				catch (error:SQLError)
				{
					trace("Error message:", error.message);
					trace("Details:", error.details);
				}
			}
			
		}
		
		public  function Init()
		{
			try
			{
				conn.open(dbFile);
				//tables();
				
			}
			catch (error:SQLError)
			{
				trace("Error message:", error.message);
				trace("Details:", error.details);
			}
		}
		
		
		public  function tables():void
		{
			
			createStmt.sqlConnection = conn;
			var sql:String = 
				"CREATE TABLE IF NOT EXISTS GibNotes (" + 
				"    uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " + 
				"    date TEXT, " 	+ 
				"    month TEXT, " 	+ 
				"    year TEXT," 	+
				"    title TEXT," 	+
				"	 text TEXT,"	+
				"	 fav  BOOL"   	+
				")";
			//alter table name addColumn "complete TEXT"
			
			createStmt.text = sql;
			createStmt.execute();
			createStmt.addEventListener(SQLEvent.RESULT, createResult);
			createStmt.addEventListener(SQLErrorEvent.ERROR, createError);
		}
		
		
		public  function createError(event:SQLErrorEvent):void
		{
			trace("SQL tables error: "+event.error);
			//Alert.show("An error occured....");
		}
		
		public  function createResult(event:SQLEvent):void
		{}
		
		var datesArr = new Array();
		public var wins = 0;
		public var total = 0;
		public var loss = 0;
		public var LastGameLost = false;
		public var dataDG;
		
		public  function nowShow(filter=false)
		{
			selectStmt.sqlConnection = conn;
			if(filter!=false)
			{
				selectStmt.text = "SELECT * FROM GibNotes WHERE name LIKE '"+filter+"%' ORDER BY fav DESC";
				//selectStmt.parameters[":filter"] = String(filter)+"%";
				//selectStmt.parameters[":filterCaps"] = String(filter).toUpperCase()+"%";
			}
			else
				selectStmt.text = "SELECT * FROM GibNotes ORDER BY fav DESC";
			
			// This try..catch block is fleshed out in
			// a subsequent code listing
			try
			{
				selectStmt.execute();
				// accessing the data is shown in a subsequent code listing
				result = selectStmt.getResult();
				
				return result;
				//if(masterList.dataProvider!=null)masterList.dataProvider.removeAll();
				
				var index = 0;
				//Utils.GibInfo.removeAll();
				//Utils.GibInfo = new ArrayList();
				//this.removeAllItems();
				
				//this.masterList.validateNow();
				
				
				
				//Utils.GibInfo.source.sort(onUID);
				//masterList.dataProvider = Utils.GibInfo;
				//masterList.validateNow();
				var obj:Object;
				//return result;
			}
			catch (error:SQLError)
			{
				trace("Error in now show: "+error.details);//Alert.show("no data for this month!");
			}
			
			
		}
		
		function onUID(obj1, obj2)
		{
			if(obj2.fav==true)
				return 1;
			else if(obj1.fav==true)
				return -1;
			else
				return 0;
		}
		
		//adding gib ...
		public  function addGibInfo(title,text,fav)
		{
			//deleteNow();
			insertStmt.sqlConnection = conn;
			var today_date:Date = new Date();
			var sql:String = 
				"INSERT INTO GibNotes (date, month, year,title,text,fav) " + 
				"VALUES (:date, :month, :year,:title,:text, :fav)";
			insertStmt.text = sql;
			//insertStmt.parameters[":ID"] = add()
			var y = new Date().fullYear;
			var date:Date = new Date();
			insertStmt.parameters[":month"] = date.getMonth();
			insertStmt.parameters[":date"] = date.date;
			insertStmt.parameters[":year"] = y;
			insertStmt.parameters[":title"] =  title;
			insertStmt.parameters[":text"] = text;
			insertStmt.parameters[":fav"] = fav;
			try
			{
				// execute the statement
				insertStmt.execute();
				
				//Alert.show("All done!");
				//nowShow();
				
			}
			catch (error:SQLError)
			{
				trace("Error occured on insert: "+error.details);//Alert.show((error.details).toString());
			}
		}
		
		
		
		
		
		// done in the nowShow function
		public function filterGibs(params)
		{
			
		}
		
		public function editGib(data,newData)
		{
			deleteNow(data.uid,true);
			addGibInfo(newData.title,newData.text,newData.fav);
		}
		
		////////////// DELETE/CHANGE FUNCTIONS ///////////////////////
		//////////////////////////////////////////////////////////////
		
		public  function deleteNow(uid,silent=false):void
		{
			deleteStmt.sqlConnection = conn;
			
			deleteStmt.text = "DELETE FROM GibNotes WHERE uid = :param1";
			deleteStmt.parameters[":param1"] = uid;
			
			// This try..catch block is fleshed out in
			// a subsequent code listing
			try
			{
				deleteStmt.execute();
			}
			catch (error:SQLError)
			{
				trace("Error on delete: "+error.details);//Alert.show("no data!");
			}
			if(!silent)
				nowShow();
		}
		
		public  function changeFavStatus(uid,status,info:Object)
		{
			deleteNow(uid,true);
			addGibInfo(info.title,info.text,status);
		}
		
	}
}